package com.sq.partner.manager.dao.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import com.sq.partner.manager.dao.TradePlanFeeDao;
import com.sq.partner.manager.mybatis.model.TradePlanFee;
import com.sq.partner.manager.util.DBUtil;
import com.sq.partner.manager.util.LogUtil;
import com.sq.partner.manager.util.Page;
import com.sq.partner.manager.util.SqlUtil;
@Repository
public class TradPlanFeeDaoImpl extends BaseDaoImpl implements TradePlanFeeDao {
	private static Logger logger = LogUtil.getLog(TradPlanFeeDaoImpl.class);

	@Override
	public Page<TradePlanFee> queryPagePlanFee(Page<TradePlanFee> page)
			throws Exception {

		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		try{
			Map<String, Object> map=page.getMap();
			conn = this.getConnection();
			StringBuffer sqlbuf=new StringBuffer();
			sqlbuf.append("SELECT * FROM (");
			sqlbuf.append(" select P.INSU_PLAN_NO plan_no,  P.OPER_TIME,P.Plan_Name, rela.agent_info_id,rela.comm_rate, rela.market_rate MARKPRE_RATE,rela.business_rate BUSPER_RATE,dep.Dept_Abr,INFO1.CHANNEL_NAME,  CONF.VHL_TYP ,   dep.dept_code, prod.core_prod_code ");
			sqlbuf.append("  from (select INSU_PLAN_NO, Plan_Name, I.AGENT_INFO_ID, P.DEPT,   P.OPER_TIME,P.Partner_No, p.Prod_Code ");
			sqlbuf.append("  FROM (select DISTINCT PLAN1.PLAN_NO INSU_PLAN_NO, PLAN1.Plan_Name, PLAN1.OPER_TIME,SUBSTR(INFO.DPT_CDE, 0, 2) DEPT,PLAN1.Partner_No, PLAN1.Prod_Code ");
			sqlbuf.append("  FROM T_NLIFE_PLAN PLAN1,  t_trad_agent_info_rela RELA,  t_trad_agent_info INFO WHERE PLAN1.PLAN_NO = RELA.INSU_PLAN_NO AND INFO.ID = RELA.AGENT_INFO_ID");
			sqlbuf.append("  AND PLAN1.AGENT_TYPE = '5' and plan1.state = '1') P ");
			sqlbuf.append("  inner join (");
			sqlbuf.append("  SELECT INFO.ID AGENT_INFO_ID,substr(INFO.DPT_CDE, 0, 2) DEPT FROM t_trad_agent_info INFO  WHERE id = ?) I");
			sqlbuf.append("  on I.DEPT = P.DEPT  AND AGENT_INFO_ID IS NOT NULL) p ");
			sqlbuf.append("  left join t_trad_agent_info_rela rela on p.insu_plan_No = rela.insu_plan_no");
			sqlbuf.append("  and p.AGENT_INFO_ID = rela.agent_info_id  left join t_core_dept dep on dep.dept_code = P.DEPT  ");
			sqlbuf.append("  LEFT JOIN t_trad_conf CONF   ON P.INSU_PLAN_NO=CONF.PLAN_NO");
			sqlbuf.append("  LEFT JOIN T_GATEWAY_CHANNEL_INFO INFO1    ON P.Partner_No=INFO1.CHANNEL_CODE ");
			sqlbuf.append("   left join t_nlife_prod prod  on  P.Prod_Code=prod.prod_code ");
			sqlbuf.append("  ) ");
			
			String sql;
			List<String> list=new ArrayList<String>();
			List<Boolean> islike=new ArrayList<Boolean>();
			/**代理ID*/
			String agentInfoId=(String) map.get("agentInfoId");
			list.add(agentInfoId);
			islike.add(false);
			sqlbuf.append(" where 1=1 ");
			if(map.get("deptCode")!=null){
				String deptCode=(String) map.get("deptCode");
				if(!"00".equals(deptCode)){
					sqlbuf.append("and  dept_code=? ");
					list.add(deptCode);
					islike.add(false);
				}
			}
			if(map.get("planNo")!=null){
				String planNo=(String) map.get("planNo");
				sqlbuf.append(" and plan_no like ?");
				list.add(planNo);
				islike.add(true);
			}
			if(map.get("planName")!=null){
				String planName=(String) map.get("planName");
				sqlbuf.append(" and  plan_Name like ?");
				list.add(planName);
				islike.add(true);
			}
			sqlbuf.append("   ORDER BY AGENT_INFO_ID,plan_NO ");
			sql=sqlbuf.toString();
			logger.debug("执行SQL：" + sql);


			pstmt = conn.prepareStatement("select count(*) from ("+sql+")");
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt.setObject(i+1, list.get(i));
				}
				
			}
			rs = pstmt.executeQuery();
			rs.next();
			Long total = rs.getLong(1);
			page.setTotal(total);
			//查出分页
			SqlUtil sqlUtil=new	SqlUtil();
			String pagesql=sqlUtil.buildPageSqlForOracle(sql,page).toString();
			logger.debug("执行SQL："+pagesql);
			pstmt1 = conn.prepareStatement(pagesql);
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt1.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt1.setObject(i+1, list.get(i));
				}
			}
			rs = pstmt1.executeQuery();
			List lists = DBUtil.transRs2List(rs);
			page.setData(lists);
		}finally{
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
		return page;
	
	}

	@Override
	public void deletePlanFeeByAgentId(String agentInfoId) throws Exception {
		this.delete("delTradPlanFee", agentInfoId);
	}

	@Override
	public boolean addTradPlanFee(List<TradePlanFee> list) throws Exception {
		try {
			this.add("insertTradPlanFeeList", list);
			return true;
		} catch (Exception e) {
			logger.info(e);
			return false;
		}
	}
}
